import openpyxl

# 加载已有的Excel文件
workbook = openpyxl.load_workbook('demo01.xlsx')

print(workbook.sheetnames)
print(workbook.active)

for sheet in workbook:
    print(sheet.title)

# 创建sheet
workbook.create_sheet('melo02')
print(workbook.sheetnames)

# DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
# sheet = workbook.get_sheet_by_name('melo02')
sheet = workbook['melo02']
print(sheet)

cell = sheet['B1']
# 获取单元格的行、列index和值
print('Row: {},Column: {} value is {}'.format(cell.row, cell.column, cell.value))
# 获取单元格的坐标
print(cell.coordinate)
# 通过横纵坐标获取单元格
print(sheet.cell(row=1, column=2).value)

# 按行维度循环遍历单元格
for row in sheet.rows:
    for cell in row:
        print(cell.value, end='\t')
print()

# 读取部分行、部分列 通过min_row、max_row、min_col、max_col参数控制，不指定则默认所有行和列
# sheet.iter_cols()功能与之类似
print(sheet.iter_rows())
for row in sheet.iter_rows():
    for cell in row:
        print(cell.value, end='\t')
print()

# 切片方式获取部分行、部分列
cell_range = sheet['A2', 'D4']
for cell in cell_range:
    pass

# 删除sheet表
workbook.remove(sheet)

workbook.save('demo01.xlsx')
